package com.dfrz.bookstore.util;

import com.dfrz.bookstore.entry.Book;
import com.dfrz.bookstore.entry.Order;
import com.dfrz.bookstore.entry.OrderItem;
import com.dfrz.bookstore.entry.User;
import com.dfrz.bookstore.service.impl.BookServiceImpl;
import com.dfrz.bookstore.service.impl.OrderItemServiceImpl;
import com.dfrz.bookstore.service.impl.OrderServiceImpl;
import com.dfrz.bookstore.service.impl.UserServiceImpl;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;

import javax.swing.*;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.List;

/**
 * 类功能描述:
 *      导出数据
 * @Author 龙柯言
 * @Date 2021/5/30 1:57
 */
public class OutputDataUtile {

    public static void main(String[] args) {
        User user = new User();
        UserServiceImpl userService = new UserServiceImpl();
        File file1 = new File("TestIO/BookStore/userInfo.xls");
        List<User> userByUser = userService.findUserByUser(user);
//        for (User user1 : userByUser) {
//            System.out.println(user1,file1);
//        }
//        outUserInfo(userByUser);
        File file2 = new File("TestIO/BookStore/BookInfo.xls");
        Book book = new Book();
        BookServiceImpl bookService = new BookServiceImpl();
        List<Book> bookList = bookService.findBookByBook(book);
//        outBookInfo(bookList,file2);

        Order order = new Order();
        OrderServiceImpl orderService = new OrderServiceImpl();
        File file3 = new File("TestIO/BookStore/orderInfo.xls");
        List<Order> orderList = orderService.findOrderByOrder(order);
        outOrderInfo(orderList,file3);
    }

    /**
     * 导出用户信息
     * @param userList
     */
    public static void outUserInfo(List<User> userList,File file){
        FileOutputStream out = null;
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
        try {
            out = new FileOutputStream(file);
            HSSFWorkbook hssfWorkbook = new HSSFWorkbook();//创建一个对象
            HSSFSheet sheet = hssfWorkbook.createSheet("用户表");

            HSSFRow row1 = sheet.createRow(0);
            HSSFCell cel1 = row1.createCell(0);
            HSSFCell cel2 = row1.createCell(1);
            HSSFCell cel3 = row1.createCell(2);
            HSSFCell cel4 = row1.createCell(3);
            HSSFCell cel5 = row1.createCell(4);
            HSSFCell cel6 = row1.createCell(5);
            HSSFCell cel7 = row1.createCell(6);
            HSSFCell cel8 = row1.createCell(7);
            HSSFCell cel9 = row1.createCell(8);
            HSSFCell cel10 = row1.createCell(9);
            HSSFCell cel11 = row1.createCell(10);
            HSSFCell cel12 = row1.createCell(11);
            cel1.setCellValue("用户ID");
            cel2.setCellValue("登录名");
            cel3.setCellValue("密码");
            cel4.setCellValue("姓名");
            cel5.setCellValue("性别");
            cel6.setCellValue("邮箱");
            cel7.setCellValue("电话号码");
            cel8.setCellValue("用户等级");
            cel9.setCellValue("用户地址");
            cel10.setCellValue("用户头像url");
            cel11.setCellValue("用户创建时间");
            cel12.setCellValue("用户修改时间");

            for (int i = 0; i < userList.size(); i++) {
                HSSFRow row = sheet.createRow(i+1);
                User user = userList.get(i);
                HSSFCell cell1 = row.createCell(0);
                HSSFCell cell2 = row.createCell(1);
                HSSFCell cell3 = row.createCell(2);
                HSSFCell cell4 = row.createCell(3);
                HSSFCell cell5 = row.createCell(4);
                HSSFCell cell6 = row.createCell(5);
                HSSFCell cell7 = row.createCell(6);
                HSSFCell cell8 = row.createCell(7);
                HSSFCell cell9 = row.createCell(8);
                HSSFCell cell10 = row.createCell(9);
                HSSFCell cell11 = row.createCell(10);
                HSSFCell cell12 = row.createCell(11);
                if (user.getId()!=0){
                    cell1.setCellValue(user.getId());
                }
                if (user.getLoginName()!=null){
                    cell2.setCellValue(user.getLoginName());
                }
                if (user.getPassWord()!=null){
                    cell3.setCellValue(user.getPassWord());
                }
                if (user.getName()!=null){
                    cell4.setCellValue(user.getName());
                }
                if (user.getSex()!=null){
                    cell5.setCellValue(user.getSex());
                }
                if (user.getEmail()!=null){
                    cell6.setCellValue(user.getEmail());
                }
                if (user.getTel()!=null){
                    cell7.setCellValue(user.getTel());
                }
                if (user.getLevel()!=0){
                    cell8.setCellValue(user.getLevel());
                }
                if (user.getAddress()!=null){
                    cell9.setCellValue(user.getAddress());
                }
                if (user.getUserImage()!=null){
                    cell10.setCellValue(user.getUserImage());
                }
                if (user.getCreate_time()!=null){
                    cell11.setCellValue(sdf.format(user.getCreate_time()));
                }
                if (user.getUpdate_time()!=null){
                    cell12.setCellValue(sdf.format(user.getUpdate_time()));
                }
            }
            hssfWorkbook.write(out);
            out.close();
            hssfWorkbook.close();
            JOptionPane.showMessageDialog(null,"导出到路径:"+file.getPath());
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }


    /**
     * 导出图书信息
     * @param bookList
     */
    public static void outBookInfo(List<Book> bookList,File file){

        FileOutputStream out = null;
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
        try {
            out = new FileOutputStream(file);
            HSSFWorkbook hssfWorkbook = new HSSFWorkbook();//创建一个对象
            HSSFSheet sheet = hssfWorkbook.createSheet("图书表");

            HSSFRow row1 = sheet.createRow(0);
            HSSFCell cel1 = row1.createCell(0);
            HSSFCell cel2 = row1.createCell(1);
            HSSFCell cel3 = row1.createCell(2);
            HSSFCell cel4 = row1.createCell(3);
            HSSFCell cel5 = row1.createCell(4);
            HSSFCell cel6 = row1.createCell(5);
            HSSFCell cel7 = row1.createCell(6);
            HSSFCell cel8 = row1.createCell(7);
            HSSFCell cel9 = row1.createCell(8);
            HSSFCell cel10 = row1.createCell(9);
            HSSFCell cel11 = row1.createCell(10);
            HSSFCell cel12 = row1.createCell(11);
            HSSFCell cel13 = row1.createCell(12);
            cel1.setCellValue("图书ID");
            cel2.setCellValue("图书名");
            cel3.setCellValue("作者");
            cel4.setCellValue("出版社");
            cel5.setCellValue("图片URL");
            cel6.setCellValue("图书价格");
            cel7.setCellValue("图书类别");
            cel8.setCellValue("是否有光碟");
            cel9.setCellValue("折扣");
            cel10.setCellValue("库存");
            cel11.setCellValue("上架状态");
            cel12.setCellValue("创建时间");
            cel13.setCellValue("修改时间");

            for (int i = 0; i < bookList.size(); i++) {
                HSSFRow row = sheet.createRow(i+1);
                Book book = bookList.get(i);
                HSSFCell cell1 = row.createCell(0);
                HSSFCell cell2 = row.createCell(1);
                HSSFCell cell3 = row.createCell(2);
                HSSFCell cell4 = row.createCell(3);
                HSSFCell cell5 = row.createCell(4);
                HSSFCell cell6 = row.createCell(5);
                HSSFCell cell7 = row.createCell(6);
                HSSFCell cell8 = row.createCell(7);
                HSSFCell cell9 = row.createCell(8);
                HSSFCell cell10 = row.createCell(9);
                HSSFCell cell11 = row.createCell(10);
                HSSFCell cell12 = row.createCell(11);
                HSSFCell cell13 = row.createCell(12);
                if (book.getId()!=0){
                    cell1.setCellValue(book.getId());
                }
                if (book.getName()!=null){
                    cell2.setCellValue(book.getName());
                }
                if (book.getAuthor()!=null){
                    cell3.setCellValue(book.getAuthor());
                }
                if (book.getPublisher()!=null){
                    cell4.setCellValue(book.getPublisher());
                }
                if (book.getImg_src()!=null){
                    cell6.setCellValue(book.getImg_src());
                }

                    cell5.setCellValue(book.getPrice());

                if (book.getType_id()!=0){
                    cell7.setCellValue(book.getType_id());
                }
                if (book.getDisc()==1){
                    cell8.setCellValue("有光碟");
                }else {
                    cell8.setCellValue("无光碟");
                }
                if (book.getDiscount()!=0){
                    cell9.setCellValue(book.getDiscount()+"折");
                }

                    cell10.setCellValue(book.getStore());

                if (book.getFlag()==1){
                    cell11.setCellValue("上架");
                }else {
                    cell11.setCellValue("下架");
                }

                if (book.getCreate_time()!=null){
                    cell12.setCellValue(sdf.format(book.getCreate_time()));
                }
                if (book.getUpdate_time()!=null){
                    cell13.setCellValue(sdf.format(book.getUpdate_time()));
                }
            }
            hssfWorkbook.write(out);
            out.close();
            hssfWorkbook.close();
            JOptionPane.showMessageDialog(null,"导出到路径:"+file.getPath());
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 导出订单信息
     * @param orderList
     */
    public static void outOrderInfo(List<Order> orderList,File file){
        OrderItemServiceImpl orderItemService = new OrderItemServiceImpl();
        BookServiceImpl bookService = new BookServiceImpl();

        FileOutputStream out = null;
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
        try {
            out = new FileOutputStream(file);
            HSSFWorkbook hssfWorkbook = new HSSFWorkbook();//创建一个对象

            HSSFCellStyle cellStyle = hssfWorkbook.createCellStyle();
            cellStyle.setFillForegroundColor(IndexedColors.RED.getIndex()); // 是设置前景色不是背景色
            cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            cellStyle.setAlignment(HorizontalAlignment.CENTER);//设置样式居中
            HSSFCellStyle cellStyle2 = hssfWorkbook.createCellStyle();
            cellStyle2.setAlignment(HorizontalAlignment.CENTER);//设置样式居中


            HSSFSheet sheet = hssfWorkbook.createSheet("订单表");

            int flag = 0; //存储当前行数  当做指针用
            for (int i = 0; i < orderList.size(); i++) {

                //创建订单表头
                HSSFRow orderRow = sheet.createRow(++flag);

                HSSFCell cel1 = orderRow.createCell(0);//订单ID
                HSSFCell cel2 = orderRow.createCell(1);//订单号
                HSSFCell cel3 = orderRow.createCell(2);//用户ID
                HSSFCell cel4 = orderRow.createCell(3);//发货状态
                HSSFCell cel5 = orderRow.createCell(4);//创建时间
                HSSFCell cel6 = orderRow.createCell(5);//修改时间
                //表头赋值
                cel1.setCellValue("订单ID");
                cel2.setCellValue("订单号");
                cel3.setCellValue("用户ID");
                cel4.setCellValue("发货状态");
                cel5.setCellValue("创建时间");
                cel6.setCellValue("修改时间");

                cel1.setCellStyle(cellStyle);
                cel2.setCellStyle(cellStyle);
                cel3.setCellStyle(cellStyle);
                cel4.setCellStyle(cellStyle);
                cel5.setCellStyle(cellStyle);
                cel6.setCellStyle(cellStyle);

                HSSFRow row = sheet.createRow(++flag); //指针加一行
                Order order = orderList.get(i);
                HSSFCell cell1 = row.createCell(0);
                HSSFCell cell2 = row.createCell(1);
                HSSFCell cell3 = row.createCell(2);
                HSSFCell cell4 = row.createCell(3);
                HSSFCell cell5 = row.createCell(4);
                HSSFCell cell6 = row.createCell(5);
                if (order.getId()!=0){
                    cell1.setCellValue(order.getId());
                }
                if (order.getOrder_item_id()!=null){
                    cell2.setCellValue(order.getOrder_item_id());
                }
                if (order.getUser_id()!=0){
                    cell3.setCellValue(order.getUser_id());
                }

                switch (order.getStatus()){
                    case 0:
                        cell4.setCellValue("未发货");
                        break;
                    case 1:
                        cell4.setCellValue("已发货");
                        break;
                    case 2:
                        cell4.setCellValue("已收货");
                        break;
                    case 3:
                        cell4.setCellValue("退货");
                        break;
                    default:
                        cel4.setCellValue("未知");
                        break;
                }

                if (order.getCreate_time()!=null){
                    cell5.setCellValue(sdf.format(order.getCreate_time()));
                }
                if (order.getUpdate_time()!=null){
                    cell6.setCellValue(sdf.format(order.getUpdate_time()));
                }

                //关联订单项
                //1.创建订单项表头
                HSSFRow orderItemRow = sheet.createRow(++flag); //指针加一行
                HSSFCell itemRowCell1 = orderItemRow.createCell(0);
                HSSFCell itemRowCell2 = orderItemRow.createCell(1);
                HSSFCell itemRowCell3 = orderItemRow.createCell(2);
                HSSFCell itemRowCell4 = orderItemRow.createCell(3);

                itemRowCell1.setCellValue("订单项ID");
                itemRowCell2.setCellValue("书本ID");
                itemRowCell3.setCellValue("书本名字");
                itemRowCell4.setCellValue("本项价格");
                //2.遍历订单项
                OrderItem orderItem = new OrderItem();
                orderItem.setOrder_id(order.getId());
                List<OrderItem> orderItemList = orderItemService.findObjByObj(orderItem);
                int sum = 0; //计算合计
                for (int j = 0; j < orderItemList.size(); j++) {
                    OrderItem orderItem1 = orderItemList.get(j);
                    Book book = new Book();
                    book.setId(orderItem1.getBook_id());
                    Book book1 = bookService.findBookByBook(book).get(0);
                    HSSFRow row1 = sheet.createRow(++flag);//指针自动增长
                    HSSFCell itemCell1 = row1.createCell(0);
                    HSSFCell itemCell2 = row1.createCell(1);
                    HSSFCell itemCell3 = row1.createCell(2);
                    HSSFCell itemCell4 = row1.createCell(3);
                    itemCell1.setCellValue(orderItem1.getId());
                    itemCell2.setCellValue(orderItem1.getBook_id());
                    itemCell3.setCellValue(book1.getName());
                    itemCell4.setCellValue(orderItem1.getPrice_sum());
                    sum+= orderItem1.getPrice_sum(); //计算总价
                }
                //3.合计
                HSSFRow sumRow= sheet.createRow(++flag);//指针加一
                HSSFCell sumRowCell = sumRow.createCell(1);
                HSSFCell sumRowCell3 = sumRow.createCell(3);
                sumRowCell.setCellValue("合计:");
                sumRowCell3.setCellValue(sum);

                flag++;//空一行
            }
            sheet.setColumnWidth(1,256*40+184);//设置行宽   一个字符长256
            sheet.setColumnWidth(2,256*40+184);//设置行宽   一个字符长256
            hssfWorkbook.write(out);
            out.close();
            hssfWorkbook.close();
            JOptionPane.showMessageDialog(null,"导出到路径:"+file.getPath());
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}
